package vip.web3.admin.utils;

import org.apache.commons.lang.BooleanUtils;
import org.apache.commons.lang.CharUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;
import vip.web3.admin.sys.annotation.ExcelField;
import vip.web3.common.utils.R;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 *
 * @Description: excel导出封装类
 * aut-horkang
 * @date 2016年8月24日
 */
public class ExcelUtils {
    private final static String EXCEL2003 = "xls";
    private final static String EXCEL2007 = "xlsx";

    // 生成excel，list导出的数据，list里的实体class，sumData合计数据
    public static <Q> XSSFWorkbook createExcel(List<Q> list, Class<Q> cls, Q sumData) throws IOException, IllegalArgumentException, IllegalAccessException {
        XSSFWorkbook wb = new XSSFWorkbook();
        Field[] fields = cls.getDeclaredFields();
        ArrayList<String> headList = new ArrayList<String>();


        // 添加合计数据
        if (sumData != null) {
            list.add(sumData);
        }


        for (Field f : fields) {
            ExcelField field = f.getAnnotation(ExcelField.class);
            if (field != null) {
                headList.add(field.title());
            }
        }


        XSSFCellStyle style = getCellStyle(wb);
        XSSFSheet sheet = wb.createSheet();
        // 设置Excel表的第一行即表头
        XSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headList.size(); i++) {
            XSSFCell headCell = row.createCell(i);
            headCell.setCellType(Cell.CELL_TYPE_STRING);
            headCell.setCellStyle(style);// 设置表头样式
            headCell.setCellValue(String.valueOf(headList.get(i)));
            // sheet.autoSizeColumn((short) i);// 设置单元格自适应
            sheet.setColumnWidth(0, 15 * 256);
        }


        for (int i = 0; i < list.size(); i++) {
            XSSFRow rowdata = sheet.createRow(i + 1);// 创建数据行
            Q q = list.get(i);
            Field[] ff = q.getClass().getDeclaredFields();
            int j = 0;
            for (Field f : ff) {
                ExcelField field = f.getAnnotation(ExcelField.class);
                if (field == null) {
                    continue;
                }
                f.setAccessible(true);
                Object obj = f.get(q);
                XSSFCell cell = rowdata.createCell(j);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                // 当数字时
                if (obj instanceof Integer) {
                    cell.setCellValue((Integer) obj);
                    // 将序号替换为123456
                 /*if (j == 0)
                        cell.setCellValue(i + 1);
                }*/
                    // 当为字符串时
                }else if (obj instanceof String) {
                     cell.setCellValue((String) obj);
                     // 当为布尔时
                 }else if (obj instanceof Boolean) {
                     cell.setCellValue((Boolean) obj);
                     // 当为时间时
                 }else if (obj instanceof Date) {
                     cell.setCellValue(DateToStirng((Date) obj));
                     // 当为时间时
                 }else if (obj instanceof Calendar) {
                     cell.setCellValue((Calendar) obj);
                     // 当为小数时
                 }else if (obj instanceof Double){
                    cell.setCellValue((Double) obj);
                 } else if (obj instanceof BigDecimal){
                    cell.setCellValue(((BigDecimal) obj).doubleValue());
                 } else if (obj instanceof Long){
                    cell.setCellValue((Long) obj);
                 }else if (obj instanceof Byte) {
                     cell.setCellValue((Byte) obj);
                 }
                j++;
            }
        }


        if (sumData != null) {
            int rowIndex = list.size();
            XSSFRow sumRow = sheet.getRow(rowIndex);
            XSSFCell sumCell = sumRow.getCell(0);
            sumCell.setCellStyle(style);
            sumCell.setCellValue("合计");
        }
        return wb;
    }

    /**
     * 导入excel
     * @param path
     * @param cls 实体类
     * @param file 数据流
     * @param <T>
     * @return
     */
    public static <T> List<T> readExcel(String path, Class<T> cls, MultipartFile file){

        String fileName = file.getOriginalFilename();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            R.error("上传文件格式不正确");
        }
        List<T> dataList = new ArrayList<>();
        Workbook workbook = null;
        try {
            InputStream is = file.getInputStream();
            if (fileName.endsWith(EXCEL2007)) {
                workbook = new XSSFWorkbook(is);
            }
            if (fileName.endsWith(EXCEL2003)) {
                workbook = new HSSFWorkbook(is);
            }
            if (workbook != null) {
                //类映射  注解 value-->bean columns
                Map<String, List<Field>> classMap = new HashMap<>();
                List<Field> fields = Stream.of(cls.getDeclaredFields()).collect(Collectors.toList());
                fields.forEach(
                        field -> {
                            ExcelField annotation = field.getAnnotation(ExcelField.class);
                            if (annotation != null) {
                                String value = annotation.title();
                                if (StringUtils.isEmpty(value)) {
                                    return;//return起到的作用和continue是相同的 语法
                                }
                                if (!classMap.containsKey(value)) {
                                    classMap.put(value, new ArrayList<>());
                                }
                                field.setAccessible(true);
                                classMap.get(value).add(field);
                            }
                        }
                );
                //索引-->columns
                Map<Integer, List<Field>> reflectionMap = new HashMap<>();
                //默认读取第一个sheet
                Sheet sheet = workbook.getSheetAt(0);

                boolean firstRow = true;
                for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
                    Row row = sheet.getRow(i);
                    //首行  提取注解
                    if (firstRow) {
                        for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                            Cell cell = row.getCell(j);
                            String cellValue = getCellValue(cell);
                            if (classMap.containsKey(cellValue)) {
                                reflectionMap.put(j, classMap.get(cellValue));
                            }
                        }
                        firstRow = false;
                    } else {
                        //忽略空白行
                        if (row == null) {
                            continue;
                        }
                        try {
                            T t = cls.newInstance();
                            //判断是否为空白行
                            boolean allBlank = true;
                            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                                if (reflectionMap.containsKey(j)) {
                                    Cell cell = row.getCell(j);
                                    String cellValue = getCellValue(cell);
                                    if (StringUtils.isNotBlank(cellValue)) {
                                        allBlank = false;
                                    }
                                    List<Field> fieldList = reflectionMap.get(j);
                                    fieldList.forEach(
                                            x -> {
                                                try {
                                                    handleField(t, cellValue, x);
                                                } catch (Exception e) {
                                                    R.error("数据错误");
                                                }
                                            }
                                    );
                                }
                            }
                            if (!allBlank) {
                                dataList.add(t);
                            } else {
                                R.error("数据错误"+i);
                            }
                        } catch (Exception e) {
                            R.error("");
                        }
                    }
                }
            }
        } catch (Exception e) {
            R.error("");
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    R.error("");
                }
            }
        }
        return dataList;
    }
    // 导出
    public static void writeExcel(HttpServletResponse response, String fileName, XSSFWorkbook wb) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("content-disposition",
                "attachment;filename=" + new String(fileName.getBytes("utf-8"),"ISO-8859-1" ));
        OutputStream ouputStream = null;
        try {
            ouputStream = response.getOutputStream();
            wb.write(ouputStream);
        } finally {
            ouputStream.close();
        }
    }


    // 表头样式
    public static XSSFCellStyle getCellStyle(XSSFWorkbook wb) {
        XSSFCellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);// 设置字体大小
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
        style.setFillForegroundColor(HSSFColor.LIME.index);// 设置背景色
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setAlignment(HSSFCellStyle.SOLID_FOREGROUND);// 让单元格居中
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
        style.setWrapText(true);// 设置自动换行
        style.setFont(font);
        return style;
    }

    public static String DateToStirng(Date date){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        return  sdf.format(date);
    }





    private static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                return HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();
            } else {
                return new BigDecimal(cell.getNumericCellValue()).toString();
            }
        } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            return StringUtils.trimToEmpty(cell.getStringCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            return StringUtils.trimToEmpty(cell.getCellFormula());
        } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
            return "";
        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
            return "ERROR";
        } else {
            return cell.toString().trim();
        }

    }

    private static <T> void handleField(T t, String value, Field field) throws Exception {
        Class<?> type = field.getType();
        if (type == null || type == void.class || StringUtils.isBlank(value)) {
            return;
        }
        if (type == Object.class) {
            field.set(t, value);
            //数字类型
        } else if (type.getSuperclass() == null || type.getSuperclass() == Number.class) {
            if (type == int.class || type == Integer.class) {
                field.set(t, NumberUtils.toInt(value));
            } else if (type == long.class || type == Long.class) {
                field.set(t, NumberUtils.toLong(value));
            } else if (type == byte.class || type == Byte.class) {
                field.set(t, NumberUtils.toByte(value));
            } else if (type == short.class || type == Short.class) {
                field.set(t, NumberUtils.toShort(value));
            } else if (type == double.class || type == Double.class) {
                field.set(t, NumberUtils.toDouble(value));
            } else if (type == float.class || type == Float.class) {
                field.set(t, NumberUtils.toFloat(value));
            } else if (type == char.class || type == Character.class) {
                field.set(t, CharUtils.toChar(value));
            } else if (type == boolean.class) {
                field.set(t, BooleanUtils.toBoolean(value));
            } else if (type == BigDecimal.class) {
                field.set(t, new BigDecimal(value));
            }
        } else if (type == Boolean.class) {
            field.set(t, BooleanUtils.toBoolean(value));
        } else if (type == Date.class) {
            //
            field.set(t, value);
        } else if (type == String.class) {
            field.set(t, value);
        } else {
            Constructor<?> constructor = type.getConstructor(String.class);
            field.set(t, constructor.newInstance(value));
        }
    }

}